﻿Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Drawing
Imports System.Collections
Imports System.Windows.Forms
Imports System.Data.Common

Module WeldSeqDao

    Function findWeldSeq(ByVal projectId As String, ByVal zone As String) As DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT SEQ_ID, PROJECT_ID, ZONE, SEQ, BARCODE" & _
              "     FROM eqam_weld_seq WHERE 1=1 "

        If (projectId IsNot Nothing And projectId <> "") Then
            SQL = SQL & " AND PROJECT_ID = @PROJECT_ID"
            paramList.Add("@PROJECT_ID", projectId)
        End If

        If (zone IsNot Nothing And zone <> "") Then
            SQL = SQL & " AND ZONE = @ZONE"
            paramList.Add("@ZONE", zone)
        End If

        SQL = SQL & " ORDER BY SEQ ASC, DATE_UPDATE DESC, TIME_UPDATE DESC"

        Return LoadData(SQL, paramList)

    End Function

    Public Function deleteWeldSeq(ByVal projectId As String, ByVal zone As String, ByVal seqs As ArrayList) As Boolean
        Dim paramList As New Hashtable()

        Dim SQL As String = "DELETE FROM eqam_weld_seq WHERE 1=1"

        If (projectId IsNot Nothing And projectId <> "") Then
            SQL = SQL & " AND PROJECT_ID = @PROJECT_ID"
            paramList.Add("@PROJECT_ID", projectId)
        End If

        If (zone IsNot Nothing And zone <> "") Then
            SQL = SQL & " AND ZONE = @ZONE"
            paramList.Add("@ZONE", zone)
        End If

        If (seqs IsNot Nothing) Then
            Dim seq As Integer
            Dim idx As Integer = 0
            If (seqs.Count > 0) Then
                SQL = SQL & " AND ("
                For Each seq In seqs
                    If (idx > 0) Then
                        SQL = SQL & " OR "
                    End If
                    SQL = SQL & " SEQ_ID = " & seq
                    idx = idx + 1
                Next
                SQL = SQL & ")"
            End If
        End If

        ExecuteSQL(sql, paramList)
    End Function

    Public Function saveWeldSeq(ByVal projectId As String, ByVal zone As String, ByVal barcode As String, ByVal seq As String) As Boolean
        Dim conn As New SqlCeConnection()
        Dim cmd As SqlCeCommand
        Dim sqlCeTransaction As SqlCeTransaction
        Dim SQL As String = ""
        Dim success As Boolean

        Try
            conn = New SqlCeConnection(sCon)

            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            sqlCeTransaction = conn.BeginTransaction
            cmd = conn.CreateCommand

            Dim paramList As New Hashtable()

            Dim isDuplicate As Boolean = False
            Dim duplicateSeqId As Integer
            Dim currentDate As Decimal = pdaUtils.getCurrentDate
            Dim currentTime As Decimal = pdaUtils.getCurrentTime

            '''''''''''''''''   check duplicate seq   '''''''''''''''''''''''            
            Dim dtResult As New DataTable
            SQL = "SELECT SEQ_ID, PROJECT_ID, ZONE, SEQ, BARCODE" & _
                  "     FROM eqam_weld_seq WHERE ZONE = @SEQ_ZONE AND SEQ >= @SEQ_SEQ AND PROJECT_ID = @SEQ_PROJECT_ID ORDER BY SEQ"
            paramList.Add("@SEQ_ZONE", zone)
            paramList.Add("@SEQ_SEQ", seq)
            paramList.Add("@SEQ_PROJECT_ID", projectId)

            dtResult = LoadData(SQL, paramList)

            Dim lastSeqNo As Integer = seq
            If dtResult.Rows.Count > 0 Then
                For Each dataRow As DataRow In dtResult.Rows
                    lastSeqNo = lastSeqNo + 1
                    isDuplicate = True
                    duplicateSeqId = dataRow.Item("SEQ_ID")

                    cmd = conn.CreateCommand
                    cmd.CommandText = "UPDATE eqam_weld_seq SET SEQ = @NEW_SEQ, DATE_UPDATE = @NEW_DATE_UPDATE, TIME_UPDATE = @NEW_TIME_UPDATE" & _
                                      " WHERE SEQ_ID = @DUPLICATE_SEQ"
                    cmd.Parameters.AddWithValue("@NEW_SEQ", lastSeqNo)
                    cmd.Parameters.AddWithValue("@DUPLICATE_SEQ", duplicateSeqId)
                    cmd.Parameters.AddWithValue("@NEW_DATE_UPDATE", currentDate)
                    cmd.Parameters.AddWithValue("@NEW_TIME_UPDATE", currentTime)

                    cmd.Transaction = sqlCeTransaction
                    cmd.ExecuteNonQuery()
                Next                
            End If
            ''''''''''''''''''''''''''''''''''''''''

            cmd = conn.CreateCommand
            cmd.CommandText = "INSERT INTO eqam_weld_seq(ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY" & _
                                ", PROJECT_ID, ZONE, SEQ, BARCODE, PDA_CODE)" & _
                              " VALUES (@ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY" & _
                                ", @PROJECT_ID, @ZONE, @SEQ, @BARCODE, @PDA_CODE)"

            'first line        
            cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
            cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
            cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
            cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
            cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
            cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            cmd.Parameters.AddWithValue("@ZONE", zone)
            cmd.Parameters.AddWithValue("@SEQ", seq)
            cmd.Parameters.AddWithValue("@BARCODE", barcode)
            cmd.Parameters.AddWithValue("@PDA_CODE", _PDA_CODE)

            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()


            ''''''''''''    REFRESH ALL SEQ ''''''''''''
            'If (isDuplicate = True) Then
            'dtResult = findWeldSeq(_PROJECT_ID, zone)
            'Dim idx As Integer = 1
            'If dtResult.Rows.Count > 0 Then
            'For Each dataRow As DataRow In dtResult.Rows
            'cmd = conn.CreateCommand
            'cmd.CommandText = "UPDATE eqam_weld_seq SET SEQ = @NEW_SEQ_A, DATE_UPDATE = @NEW_DATE_UPDATE_A, TIME_UPDATE = @NEW_TIME_UPDATE_A" & _
            '                  " WHERE SEQ_ID = @SEQ_ID_DB"
            'cmd.Parameters.AddWithValue("@NEW_SEQ_A", idx)
            'cmd.Parameters.AddWithValue("@SEQ_ID_DB", DataRow.Item("SEQ_ID"))
            'cmd.Parameters.AddWithValue("@NEW_DATE_UPDATE_A", currentDate)
            'cmd.Parameters.AddWithValue("@NEW_TIME_UPDATE_A", currentTime)

            'cmd.Transaction = sqlCeTransaction
            'cmd.ExecuteNonQuery()

            'idx = idx + 1
            'Next
            'End If
            'seq = idx - 1
            'End If
            ''''''''''''''''''''''''''''''''''''''''''''
            Dim zoneSQL As String = "UPDATE eqam_zone " & _
                    " SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY, SEQ = @SEQ WHERE ZONE = @ZONE AND PROJECT_ID = @PROJECT_ID"
            cmd = conn.CreateCommand
            cmd.CommandText = zoneSQL
            cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
            cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
            cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)
            cmd.Parameters.AddWithValue("@SEQ", lastSeqNo + 1)
            cmd.Parameters.AddWithValue("@ZONE", zone)
            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)

            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()
            sqlCeTransaction.Commit()

            success = True
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, sysname & " - ex")
            MsgBox("LOCAL CONNECTION CREATE COUPON: " & vbCrLf & ex.Message, MsgBoxStyle.Information, sysname & " - ExecuteSQL")
            sqlCeTransaction.Rollback()
            success = False
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try
        Return success
    End Function
End Module
